package 人事管理系统.Connect;

import 人事管理系统.Tables.Accounts;
import 人事管理系统.Tables.Employee;

import javax.swing.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.*;
import java.util.ArrayList;

public class Employee_Database {
    Employee emp=new Employee();
    private  String ip=this.getIp();
    private String url="jdbc:mysql://"+ip+":3306/人事管理系统?useOldAliasMetadataBehavior=true";
    //获取ip
    public String getIp(){
        InetAddress addr = null;
        try {
            addr = InetAddress.getLocalHost();
        } catch (UnknownHostException e) {
            e.printStackTrace();
        }
        System.out.println("Local HostAddress: "+addr.getHostAddress());
        String hostname = addr.getHostName();
        System.out.println("Local host name: "+hostname);
        return addr.getHostAddress();
    }


/**********************************************************************************************************************/

    /*
   查询所有员工信息
    */
    public ArrayList<Employee> findAll()   {
        ArrayList<Employee> list=new ArrayList<>();
        Connection con=null;
        Statement stat=null;
        ResultSet rs =null;
        try {
            //1.导入jar包
            //2.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3.获取连接
            con= DriverManager.getConnection(url,"root","020214YxY");
            //4.获取执行者对象
            stat=con.createStatement();
            //5.执行sql语句，并接收结果
            String sql="SELECT * FROM employee ";
            rs = stat.executeQuery(sql);
            //6.处理结果

            while(rs.next()){
                //封装对象
                Employee ee=new Employee();
                ee.setEno(rs.getString("ENO"));
                ee.setEname(rs.getString("ENAME"));
                ee.setSex(rs.getString("SEX"));
                ee.setEducation(rs.getString("education_background"));
                ee.setDno(rs.getString("Dno"));
                ee.setDuty(rs.getString("Duty"));
                list.add(ee);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //7.释放资源
            if(con!=null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs!=null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(stat!=null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

        return list;
    }

/**********************************************************************************************************************/
 /*
    条件查询，根据Eno查询员工信息
     */
public  Employee findByEno(String Eno){
    Employee ee=new Employee();
    Connection con=null;
    Statement stat=null;
    ResultSet rs =null;
    try {
        //1.导入jar包
        //2.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //3.获取连接
        con= DriverManager.getConnection(url,"root","020214YxY");
        //4.获取执行者对象
        stat=con.createStatement();
        //5.执行sql语句，并接收结果
        String sql="SELECT * FROM employee WHERE ENO= '"+Eno+"'";
        rs = stat.executeQuery(sql);
        //6.处理结果

        while(rs.next()){

            ee.setEno(rs.getString("ENO"));
            ee.setEname(rs.getString("ENAME"));
            ee.setSex(rs.getString("SEX"));
            ee.setEducation(rs.getString("education_background"));
            ee.setDno(rs.getString("Dno"));
            ee.setDuty(rs.getString("Duty"));
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        //7.释放资源
        if(con!=null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stat!=null) {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
    return ee;
}
/**********************************************************************************************************************/
   /*
    插入一条员工信息
     */
public  int insert(Employee emp){

    Connection con=null;
    Statement stat=null;
    int result=0;//修改的行数
    try {
        //1.导入jar包
        //2.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //3.获取连接
        con= DriverManager.getConnection(url,"root","020214YxY");
        //4.获取执行者对象
        stat=con.createStatement();
        //5.执行sql语句，并接收结果
        // SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
        //String sql="INSERT INTO employee VALUES ('"+emp.getEno()+"','"+emp.getEname()+"','"+emp.getSex()+"','"+emp.getEducation()+"','"+emp.getDno()+"','"+emp.getDuty()+"')";
        String sql="call Insert_employee('"+emp.getEno()+"','"+emp.getEname()+"','"+emp.getSex()+"','"+emp.getEducation()+"','"+emp.getDno()+"','"+emp.getDuty()+"')";
        result=stat.executeUpdate(sql);
        //6.处理结果

    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        //成功弹窗
        //new opyes();
        //7.释放资源
        if(con!=null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stat!=null) {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
    return result;
}
/**********************************************************************************************************************/
   /*
    删除员工信息
     */
public  int delete(String Eno){
    Connection con=null;
    Statement stat=null;
    int result=0;//修改的行数
    try {
        //1.导入jar包
        //2.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //3.获取连接
        con= DriverManager.getConnection(url,"root","020214YxY");
        //4.获取执行者对象
        stat=con.createStatement();
        //5.执行sql语句，并接收结果
        //String sql="DELETE FROM employee WHERE ENO='"+Eno+"'";
        String sql="call Delete_employee('"+Eno+"')";
        result=stat.executeUpdate(sql);
        //6.处理结果
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
//        //成功弹窗
//        if(result>0)
//            new opyes();
//        else new opno();
        //7.释放资源
        if(con!=null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stat!=null) {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
    return result;
}
/**********************************************************************************************************************/
    /*
    修改商品信息
     */

public  int updata(Employee gds){
    Connection con=null;
    Statement stat=null;
    int result=0;//修改的行数
    try {
        //1.导入jar包
        //2.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //3.获取连接
        con= DriverManager.getConnection(url,"root","020214YxY");
        //4.获取执行者对象
        stat=con.createStatement();
        //5.执行sql语句，并接收结果
        // SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
        String sql="UPDATE employee SET ENO='"+gds.getEno()+"',ENAME='"+gds.getEname()+"',SEX='"+gds.getSex()+"',education_background='"+gds.getEducation()+"',DNO='"+gds.getDno()+"',Duty='"+gds.getDuty()+"'WHERE ENO='"+gds.getEno()+"'";
        result=stat.executeUpdate(sql);
        //6.处理结果
        //   '"+gds.getGoodsNum()+"','"+gds.getGoodsName()+"','"+gds.getGoodsPrice()+"','"+gds.getGoodsListingDate()+"','"+gds.getGoodsCategories()+"','"+gds.getGoodsOrigin()+"'
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        //成功弹窗
        if (result>0)
            new opyes();
        else new opno();
        //7.释放资源
        if(con!=null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stat!=null) {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
    return result;
}



/**********************************************************************************************************************/
    //操作成功弹窗
    class opyes{
        public opyes(){
            // 创建 JFrame 实例
            JFrame frame = new JFrame("系统提示");
            // Setting the width and height of frame
            frame.setSize(350, 200);
            frame.setLocation(500,250);
            frame.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
            frame.setVisible(true);
            JButton j=new JButton("操作成功！！！");
            frame.add(j);
            j.addActionListener(new ActionListener() {
                @Override
                public void actionPerformed(ActionEvent e) {

                }
            });


        }
    }
    //操作失败弹窗
    class opno{
        public opno(){
            // 创建 JFrame 实例
            JFrame frame = new JFrame("系统提示");
            // Setting the width and height of frame
            frame.setSize(350, 200);
            frame.setLocation(500,250);
            frame.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
            frame.setVisible(true);
            JButton j=new JButton("没有该项数据！！！");
            frame.add(j);
            j.addActionListener(new ActionListener() {
                @Override
                public void actionPerformed(ActionEvent e) {

                }
            });}}
}
